﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Net;
using System.Text;
using CRMEntity;
using CRMFramework;

namespace CRMBiz
{
    public static class CmsMemberHandler
    {
        public static CmsMember Login(string userName, string password)
        {
            try
            {
                var p = new[] {
                    DBhelper.SetParameter("@username", userName),
                    DBhelper.SetParameter("@password", password)
                };

                var sbSql = new StringBuilder("SELECT m.*, n.text AS MemberName, n.createDate")
                    .Append(" FROM CmsMember m, UmbracoNode n")
                    .Append(" WHERE m.nodeId = n.id")
                    .Append(" AND m.LoginName=@username")
                    .Append(" AND m.Password=@password");

                var dt = DBhelper.ExecuteDataset(sbSql.ToString(), p).Tables[0];

                if (dt.Rows.Count == 0)
                {
                    return null;
                }

                var entity = new CmsMember();
                var dr = dt.Rows[0];
                entity.NodeID = dr["NodeId"] as int?;
                entity.Email = dr["Email"] as string;
                entity.LoginName = dr["LoginName"] as string;
                entity.MemberName = dr["MemberName"] as string;
                entity.CreateDate = dr["CreateDate"] as DateTime?;

                return entity;
            }
            catch (Exception ex)
            {
                //return ex.Message;
                throw ex;
            }
        }

        public static CmsMember GetByUserName(string userName)
        {
            try
            {
                var p = new[] {
                    DBhelper.SetParameter("@username", userName),
                };

                var sbSql = new StringBuilder("SELECT m.*, n.text AS MemberName, n.createDate")
                    .Append(" FROM CmsMember m, UmbracoNode n")
                    .Append(" WHERE m.nodeId = n.id")
                    .Append(" AND m.LoginName=@username");

                var dt = DBhelper.ExecuteDataset(sbSql.ToString(), p).Tables[0];

                if (dt.Rows.Count == 0)
                {
                    return null;
                }

                var entity = new CmsMember();
                var dr = dt.Rows[0];
                entity.NodeID = dr["NodeId"] as int?;
                entity.Email = dr["Email"] as string;
                entity.LoginName = dr["LoginName"] as string;
                entity.MemberName = dr["MemberName"] as string;
                entity.CreateDate = dr["CreateDate"] as DateTime?;

                return entity;
            }
            catch (Exception ex)
            {
                //return ex.Message;
                throw ex;
            }
        }

        public static List<CmsMember> GetAllMembers()
        {
            try
            {
                var sbSql = new StringBuilder("SELECT mg.id NodeId, mg.text MemberName, mg.CreateDate")
                    .Append(" FROM umbraconode mg")
                    .Append(" WHERE mg.nodeObjectType = '39eb0f98-b348-42a1-8662-e7eb18487560'");
                DataTable dt;            
                dt = DBhelper.ExecuteDataset(sbSql.ToString()).Tables[0];
               
                var list = new List<CmsMember>();
                foreach (DataRow dr in dt.Rows)
                {
                    var entity = new CmsMember
                    {
                        NodeID = dr["NodeId"] as int?,
                        MemberName = dr["MemberName"] as string,
                        CreateDate = dr["CreateDate"] as DateTime?
                    };
                    list.Add(entity);
                }

                return list;         
        }
            catch (Exception ex)
            {
                //return ex.Message;
                throw ex;
            }
        }

        /// <summary>
        /// function to check IP Address 
        /// </summary>
        /// <param name="IPAddress"></param>
        /// <returns>status</returns>
        public static int GetIPAddress(string userName, string password)
        {
            string IPAddress = System.Web.HttpContext.Current.Request.UserHostAddress;
            int intReturnValue = 0;
            try
            {
                //If User is Admin:
                if (userName.ToLower() == "admin")
                {
                    intReturnValue = 1;
                }
                else
                {
                    var para = new[] {
                    DBhelper.SetParameter("@username", userName),
                    DBhelper.SetParameter("@password", password)
                };
                    //Check username and password:
                    string sbSql = "Select * From cmsmember Where LoginName = @username and Password=@password";
                    DataSet ds = DBhelper.ExecuteDataset(sbSql, para);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        var paraIp = new[] {
                        DBhelper.SetParameter("@IPAddress", IPAddress),
                };
                        //Check Ip address is exist or not:
                        string sbSqlIp = "Select * From crm_allowedIP Where IPAddress = @IPAddress";
                        DataSet dsIp = DBhelper.ExecuteDataset(sbSqlIp, paraIp);
                        if (dsIp.Tables[0].Rows.Count > 0)
                        {
                            intReturnValue = 1;
                        }
                        else
                        {
                            intReturnValue = 0;
                        }
                    }
                    else
                    {
                        intReturnValue = 0;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        // <summary>
        // function to get user IP Address
        // </summary>
        // <returns>ip</returns>
        //public static string GetUserIP()
        //{
        //    string ip = "";
        //    IPHostEntry ipEntry = Dns.GetHostEntry(GetSysCode());
        //    IPAddress[] addr = ipEntry.AddressList;
        //    ip = addr[2].ToString();
        //    return ip;
        //}

        // <summary>
        // function to get host name
        // </summary>
        // <returns>host name</returns>
        //public static string GetSysCode()
        //{
        //    string strHostName = "";
        //    strHostName = Dns.GetHostName();
        //    return strHostName;
        //}
    }


}
